﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace Tools {
    /// <summary>
    /// 处理Office常见文档Word、Excel
    /// HomePage:http://npoi.codeplex.com/
    /// </summary>
    public static class OfficeHelper {
        /// <summary>
        ///   保存office2003格式的Excel
        /// </summary>
        /// <param name="fileName">保存的绝对路径文件名 (office2003 - office2007)</param>
        /// <param name="sheetName">Exl表名</param>
        /// <param name="source">数据源</param>
        /// <param name="attributeNames">要生成的数据源参数名</param>
        public static void SaveExl(string fileName, string sheetName, IEnumerable<object> source, string[] attributeNames) {
            var excel = new HSSFWorkbook();
            ISheet sheet = excel.CreateSheet(sheetName);
            var srcList = source.ToList();//TODO: 数据结构 查询快 定位快 

            for (int i = 0; i < srcList.Count; i++) {
                IRow row = sheet.CreateRow(i);
                var data = srcList[i];
                Type objType = data.GetType();
                for (int j = 0; j < attributeNames.Length; j++) {
                    PropertyInfo propertyInfo = objType.GetProperty(attributeNames[j]);
                    if (propertyInfo != null) {
                        row.CreateCell(j).SetCellValue(propertyInfo.GetValue(data, null).ToString());
                    }
                }
            }

            /* 写入到客户端  
            using (var ms = new System.IO.MemoryStream())
            {
                book.Write(ms);
                Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
                Response.BinaryWrite(ms.ToArray());
                book = null;
                ms.Close();
                
            }
            */

            /******
             * 文件流
             * 
             * 
             */
            using (var stream = new FileStream(fileName, FileMode.Create)) {
                excel.Write(stream);
            }
        }

        /// <summary>
        /// 保存EXCEL
        /// </summary>
        /// <param name="fileName">保存的绝对路径文件名 (office2003 - office2007)</param>
        /// <param name="sheetName">Exl表名</param>
        /// <param name="columnVals">每列数值，以第一个参数为标准（）</param>
        public static void SaveExlByColumns(string fileName, string sheetName, params string[][] columnVals) {
            var excel = new HSSFWorkbook();
            ISheet sheet = excel.CreateSheet(sheetName);
            var rowCount = columnVals.Length;
            for (int i = 0; i < rowCount; i++) {
                IRow row = sheet.CreateRow(i);
                for (int j = 0; j < columnVals[0].Length; j++) {
                    //for (int k = 0; k < columnVals.Length; k++) {
                    if(columnVals[i]==null)
                    { 
                        break;
                    }
                    else
                    {
                        row.CreateCell(j).SetCellValue(columnVals[i][j]);
                    }
                    //}
                }
            }

            using (var stream = new FileStream(fileName, FileMode.Create)) {
                excel.Write(stream);
            }
        }



        /// <summary>
        /// 根据相对路径进行excle导入  支持2003版本的excle
        /// </summary>
        /// <param name="excelFileStream">excel文件流</param>
        /// <returns>返回DateTable的list集合</returns>
        public static IList<DataTable> InputExl(Stream excelFileStream) {
            //第一行一般为标题行。
            List<DataTable> tableList = new List<DataTable>();

            //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
            HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
            //获取excel的sheet数量
            int sheelNum = workbook.Workbook.NumSheets;
            //遍历整个excel，将所有excel的每一个sheet转为DataTable存入List<DataTable> 
            for (int k = 0; k < sheelNum; k++) {
                DataTable table = new DataTable();
                //获取excel的第一个sheet
                HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(k);

                //获取Excel的最大行数
                int rowsCount = sheet.PhysicalNumberOfRows;

                //为保证Table布局与Excel一样，这里应该取所有行中的最大列数（需要遍历整个Sheet）。
                //为少一交全Excel遍历，提高性能，我们可以人为把第0行的列数调整至所有行中的最大列数。
                int colsCount = sheet.GetRow(0).PhysicalNumberOfCells;
                for (int i = 0; i < colsCount; i++) {
                    table.Columns.Add(i.ToString());
                }
                for (int x = 0; x < rowsCount; x++) {
                    DataRow dr = table.NewRow();
                    for (int y = 0; y < colsCount; y++) {
                        var temp = sheet.GetRow(x).GetCell(y);
                        dr[y] = (temp == null) ? "-" : sheet.GetRow(x).GetCell(y).ToString();
                        if (String.IsNullOrEmpty(dr[y].ToString())) {
                            dr[y] = "-";
                        }


                    }
                    table.Rows.Add(dr);
                }
                tableList.Add(table);
            }
            return tableList;
        }

    }
}
